GAS初心者がスプレッドシートでマクロからスクリプトを作ってみた

CSVデータを整形したい事案があり、Googleスプレッドシートのスクリプトを使ってみました。タイトルの通りマクロをベースにして簡単な処理を作ったので、今回のエントリではその流れから初心者の方でも始められるコツみたいなところを紹介できたらと思います。

背景

ご参考までにスプレッドシートでやろうと思った背景を。

弊社クラメソのヨーロッパはまだまだ小さい拠点でして、社内のツールでもより良いサービスがあればどんどん試してみますし、これまでにも会計ツールや決済システム、銀行などが切り替わりました。一方で、会計ツールや銀行を変えたことで自動連携がサポートされず、一部で取引明細のCSVを銀行からエクスポートし、CSVを会計ツールへ取り込まなければいけなくなりました。
そのCSVですが、ある程度の規格は似てるものの、銀行によって列の値の形式が違ったり、会計ツールによって列のnullが許容されなかったりと、CSVの整形が必要になりました。

これは一例ですが、現状会計に関する作業では他にも異なる形式や期間限定でCSVを扱う機会が多く、サクッと導入できる方法としてとりあえずスプレッドシートを使うことにしました。
なお、将来的には Python 等のコード処理へ移行させたいのですが、お勉強と作り込みに時間を要するので、マニュアル作業が残るのを許容しつつ、手軽に、少しでも自動化させたい、という方式でGASを採用しました。

こちらのブログで Python でより効率的に実装した方法をご紹介しました。

流れ

この処理で最終的にやっていることの流れです。上記の背景がありますので、調べるのに時間がかかる部分はマニュアルとして残しており、自動化として完全性がないところはご容赦くださいませ。

1. (事前準備/マニュアル) エクスポートしたCSVを列ごとにコピペ
2. 指定列のタイムスタンプの表示形式を変更
3. 1列追加し、既存の列を編集した結果を、追加した列に表示させる x 3回
4. 残す列のヘッダの名前を更新し、不要な列を削除
5. (後処理/マニュアル) 整形した内容をCSV保存

これを作る大雑把な流れとしては、「マクロを記録し、スクリプトを見て読解し、必要に応じて修正する」作業を繰り返し、最後にすべてのマクロを一つに統合するだけ。

当初は似た処理を外部のサイトから探し、もろパクリして必要な部分を修正しようと思っていました。これが一番効率的だと思います。残念ながら、今回は結構コアな内容で、やりたいことに近い参考サイトを見つけられず、大幅な修正を一から調べていくことの方が大変でした。そのため、チュートリアルなんかもすっ飛ばして、マクロを元にちょっとずつ勉強しながら実現しました。

ポイントは、簡単な処理（指定列の選択、列の削除、など）から始めて少しずつ理解していき、読解&修正の部分でだけドキュメントや外部の参考サイトを参照することです。

マクロを記録し、スクリプトを確認する

早速やってみましょう。なお、CSVは「ファイル」→「インポート」からもできますが、全データをコピペして「テキストを列に分割」を選択すると簡単に列に分けられます。

「ツール」→「マクロ」→「マクロを記録」を選択すると、下部にこのような記録中のウィンドウが出るので、その間記録する操作を行います。今回は、C/E列、F列、G列の削除を順に行いました。終わったら、「保存」。

なんとなく想像つくと思いますが、絶対参照は「G列」のような指定の仕方に対し、相対参照はマクロ実行時に選択しているセルの場所から「2つ先の列」のような指定の仕方です。

テキトーに名前をつけます。

もう一度「ツール」→「マクロ」を開くと、保存したマクロができています。これを選択するだけで、C/E列、F列、G列の削除が行われるようになりました。

続けてもう一つマクロを記録してしまいましょう。

同様にマクロの記録で、今回は、D列を選択し、「表示形式」→「数字」→「詳細設定」→「その他の日付や時刻の形式」で次の通りに設定しました。元の DD-MM-YYYY 形式を YYYY-MM-DD へ表示を切り替えるよう指定しています。

「ツール」→「スクリプトエディタ」を開きます。

1つ目のマクロ（deleteColumns）と2つ目のマクロ（updateTimestamp）がそれぞれ記録されました！

スクリプトを読解し、修正する

では上のスクリプトを確認しましょう。

ふむふむ、アクティブなシートを取得して、そのうち特定の列を選択して、選択範囲の削除処理やタイムスタンプ変更を行なっているのね。

ところがこちら、カラム削除の方は、もう一度マクロを実行してみると思った通りに動きませんでした。C/E列を同時に選択して削除を行いましたが、E列しか削除されません。実は、スクリプトによると spreadsheet.getRangeList(['C:C', 'E:E']).activate(); でC/E列を選択していますが、次の処理でアクティブなカラム（E列）からカラムの数（1列）だけ削除すると定義されていました。

これを理解するには、使用しているメソッドの正確な意味を確認していく必要があります。

「ヘルプ」→「APIリファレンス」をクリックします。例えば、"deleteColumns" であれば右上のSearchボックスから検索してヒットした1つ目のページでさらに文字列検索し、たどり着きました。

このメソッドでは1つ目の引数で指定する列を数字で示し（Aなら1など）、2つ目の引数で指定した列から削除する列数を示すようです。
その他、getActiveRange() は選択された範囲を返し、getColumn() はその範囲の開始列の位置を返します。リファレンスに載っている例も参考にしながら修復していきます。

これでは連続する列しか選択できないようなので、とりあえず1列ずつ削除するように修正してみました。

シートに戻ると、このままではスクリプトから追加した関数「deleteColumns2」はマクロの一覧には表示されません。「ツール」→「マクロ」→「インポート」で [関数を追加] をクリックすると、マクロの一覧に追加されます。スクリプト側で関数の名前を変更した場合も、このようにインポートが必要です。

繰り返す

流れとしてはこのような形で、あとはトライアンドエラーです。

記録したマクロがややこしくて読解できない場合は、今回のように複数のアクションを同時に記録するのではなく、例えばセルの移動だけなど簡単なアクションで分けて記録し、一つずつ理解していきましょう。

ご参考

ちなみに、今回できあがった仕上がりはこちらです。updateTimestamp 以降の関数で、冒頭に記載した 2、3 (x 3回)、4 をそれぞれ定義し、updateRecords ですべてまとめて実行できるようにしています。

マクロをベースにしており非効率な箇所も多く、コードの見栄えとしては最低だと思うので、長く運用していくなら可視性を高くブラッシュアップすべきです。今回のケースでは、改変の可能性はほぼなく、データ量も少ないので、しばらくはこれで。

function updateRecords() {
  updateTimestamp();
  updateRecordOne();
  updateRecordTwo();
  updateRecordThree();
  deleteColumns();
}

function updateTimestamp() {
  var ss = SpreadsheetApp.getActive();
  // 対象の列を日付フォーマット変換
  ss.getRangeList(['D:D', 'F:F']).activate()
  .setNumberFormat('yyyy"-"mm"-"dd" "hh":"mm":"ss');
};

function updateRecordOne() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  // H列を選択し、左に１列追加
  ss.getRange('H:H').activate();
  ss.getActiveSheet().insertColumnsBefore(ss.getActiveRange().getColumn(), 1);
  ss.getActiveRange().offset(0, 0, ss.getActiveRange().getNumRows(), 1).activate();

  // H1にヘッダー名を追加
  ss.getRange('H1').activate();
  ss.getCurrentCell().setValue('Verwendungszweck');
  
  // H2から最終行のセルまで選択
  sheet.getRange(2, 8, sheet.getLastRow() - 1, 1).activate();
  
  // アクティブ範囲に関数を定義
  ss.getActiveRange().setFormulaR1C1('=IF(R[0]C[4]="",R[0]C[1]&" "&R[0]C[2],R[0]C[4])');
  SpreadsheetApp.flush();
  ss.getActiveRange().copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  
};

function updateRecordTwo() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  // N列を選択し、左に１列追加
  ss.getRange('N:N').activate();
  ss.getActiveSheet().insertColumnsBefore(ss.getActiveRange().getColumn(), 1);
  ss.getActiveRange().offset(0, 0, ss.getActiveRange().getNumRows(), 1).activate();

  // N1にヘッダー名を追加
  ss.getRange('N1').activate();
  ss.getCurrentCell().setValue('Betrag');
  
  // N2から最終行のセルまで選択
  sheet.getRange(2, 14, sheet.getLastRow() - 1, 1).activate();
  
  // アクティブ範囲に関数を定義
  ss.getActiveRange().setFormulaR1C1('=-R[0]C[1]');
  SpreadsheetApp.flush();
  ss.getActiveRange().copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  
  
};

function updateRecordThree() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  // Q列を選択し、左に１列追加
  ss.getRange('Q:Q').activate();
  ss.getActiveSheet().insertColumnsBefore(ss.getActiveRange().getColumn(), 1);
  ss.getActiveRange().offset(0, 0, ss.getActiveRange().getNumRows(), 1).activate();

  // Q1にヘッダー名を追加
  ss.getRange('Q1').activate();
  ss.getCurrentCell().setValue('Zusatzinfo');
  
  // Q2から最終行のセルまで選択
  sheet.getRange(2, 17, sheet.getLastRow() - 1, 1).activate();
  
  // アクティブ範囲に関数を定義
  ss.getActiveRange().setFormulaR1C1('=IF(R[0]C[2]="EUR",,R[0]C[1]&" "&R[0]C[2]&" ")&R[0]C[3]&" "&R[0]C[4]');
  SpreadsheetApp.flush();
  ss.getActiveRange().copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  
  
};


function deleteColumns() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  // rename header
  ss.getRange('D1').activate();
  ss.getCurrentCell().setValue('Wertstellungsdatum');
  ss.getRange('F1').activate();
  ss.getCurrentCell().setValue('Buchungsdatum');
  ss.getRange('G1').activate();
  ss.getCurrentCell().setValue('Auftraggeber/Empfänger');

  // delete unrequired columns 
  ss.getRange('R:X').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('O:P').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('I:M').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('E:E').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('A:C').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('A1').activate();
};

補足

※フォーマットはそのまま、データはサンプルです。

マクロを実行すると次の通りです。

updateRecordXXXでは、最終行の取得に getLastRow() を使い、データのある最後の行（この場合、8）を取得しています。似たようなので getMaxRows() がありますが、こちらはシート上の空の行も含めた最終行の数を返すようです。
また、setFormulaR1C1() で選択範囲に関数を定義し、次の copyTo() でその関数を値で貼り付けしています。

この部分に関してはまりどこだったのが、

  ss.getActiveRange().setFormulaR1C1('=IF(R[0]C[4]="",R[0]C[1]&" "&R[0]C[2],R[0]C[4])')
  ss.getActiveRange().copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  

上の場合、処理がきちんと最後まで動きませんでした。2行の間にセル移動など他の処理を挟むとうまくいきます。

「getActiveRange().copyTo cannot be copied」とググってみたら、似たような事象のページが見つかり、これを参考に SpreadsheetApp.flush(); を間に入れてみたら動きました。どうやら2つ目の処理が間に合っていないようでした。

• flush() - Google Developers

以上、マクロって記録してみても思った通り動かない印象があったんですが、使ってみるとこんなに簡単に導入できて割とやりたいことちゃんとできるじゃん！ってことで今さらの発見でした。

少しでもどなたかの役に立てば幸いです！